﻿Public Class AccesoDatos
    'Data Source=FRANCO-NB\SQL2008;Initial Catalog=bd_destroy;Integrated Security=True
    'Data Source=DANI-NB\SQLEXPRESS2;Integrated Security=SSPI;Initial Catalog=bd_destroy
    'Data Source=FABUR-PC\SQLEXPRESS;Initial Catalog=bd_destroy;Integrated Security=True
    'Data Source=nb;Initial Catalog=bd_destroy;Integrated Security=True

    Dim conexion As New SqlClient.SqlConnection
    Dim cmd As New SqlClient.SqlCommand
    Dim nombreTabla As String


    Dim stringConexion As String = "Data Source=FRANCO-NB\SQL2008;Initial Catalog=bd_destroy;Integrated Security=True"

    Public Sub New(ByVal tabla As String)
        Me.nombreTabla = tabla
    End Sub

    Private Sub conectar()
        conexion.ConnectionString = stringConexion
        conexion.Open()
        cmd.Connection = conexion
        cmd.CommandType = CommandType.Text
    End Sub

    Public Function validarExistencia(ByVal nombreTabla As String, ByVal nombrePk As String, ByVal pk As String) As Boolean
        Dim tabla As New Data.DataTable

        Me.conectar()
        cmd.CommandText = "select * from " & nombreTabla & " where " & nombrePk & "= " & pk
        tabla.Load(cmd.ExecuteReader())
        conexion.Close()

        If tabla.Rows.Count() = 1 Then
            Return True
        Else
            Return False
        End If
    End Function


    Public Function leerTabla(ByVal tablaALeer As String) As Data.DataTable
        Dim tabla As New Data.DataTable
        Me.conectar()
        cmd.CommandText = "select * from " & tablaALeer

        tabla.Load(cmd.ExecuteReader())
        conexion.Close()
        Return tabla
    End Function

    Public Function leerProducto() As Data.DataTable
        Dim tabla As New Data.DataTable
        Me.conectar()
        cmd.CommandText = "select prod.idProducto as 'ID', prov.nombre as 'Proveedor', tp.nombre as 'Tipo Producto', tt.nombre as 'Tela',  prod.descripcion as 'Descripcion' "
        cmd.CommandText &= "from Producto prod INNER JOIN Proveedor prov on(prod.idProveedor=prov.idProveedor) "
        cmd.CommandText &= "INNER JOIN TipoTela tt on (tt.idTipoTela=prod.idTipoTela) "
        cmd.CommandText &= "INNER JOIN TipoProd tp on (tp.idTipoProd=prod.idTipoProd) "
        tabla.Load(cmd.ExecuteReader())
        conexion.Close()
        Return tabla
    End Function

    Public Function leerProveedor() As Data.DataTable
        Dim tabla As New Data.DataTable
        Me.conectar()
        cmd.CommandText = "select prov.idProveedor as 'ID', prov.nombre as 'Proveedor' from Proveedor prov"
        tabla.Load(cmd.ExecuteReader())
        conexion.Close()
        Return tabla
    End Function


    Public Function leerEmpleado() As Data.DataTable
        Dim tabla As New Data.DataTable
        Me.conectar()
        cmd.CommandText = "select e.idEmpleado as 'Legajo', e.apellido as 'Apellido', e.nombre as 'Nombre' from Empleado e"
        tabla.Load(cmd.ExecuteReader())
        conexion.Close()
        Return tabla

    End Function

    Public Function leerTela() As Data.DataTable
        Dim tabla As New Data.DataTable
        Me.conectar()
        cmd.CommandText = "select tela.idTipoTela as 'ID Tela', tela.nombre as 'Nombre Tela' from TipoTela tela"
        tabla.Load(cmd.ExecuteReader())
        conexion.Close()
        Return tabla
    End Function

    Public Function leerBanco() As Data.DataTable
        Dim tabla As New Data.DataTable
        Me.conectar()
        cmd.CommandText = "select banco.idBanco as 'ID Banco', banco.nombre as 'Nombre banco' from Banco banco"
        tabla.Load(cmd.ExecuteReader())
        conexion.Close()
        Return tabla
    End Function

    Public Function leerCuentasBancarias(ByVal id As String) As Data.DataTable
        Dim tabla As New Data.DataTable
        Me.conectar()
        cmd.CommandText = "select c.nroCuenta, c.nombre, b.nombre as 'Banco'  from Cuenta_Bancaria c INNER JOIN Banco b on (c.idBanco = b.idBanco) WHERE c.idProveedor = " & id
        tabla.Load(cmd.ExecuteReader())
        conexion.Close()
        Return tabla
    End Function

    Public Function leerUsuario() As Data.DataTable
        Dim tabla As New Data.DataTable
        Me.conectar()
        cmd.CommandText = "select u.idUsuario, u.nombre as 'Nombre Usuario', e.nombre as 'Nombre Empleado', e.apellido as 'Apellido Empleado' from Usuario u INNER JOIN Empleado e on (e.idEmpleado = u.idEmpleado)"
        tabla.Load(cmd.ExecuteReader())
        conexion.Close()
        Return tabla
    End Function

    Public Sub eliminar(ByVal campo As String, ByVal valor As String, ByVal campo2 As String, ByVal valor2 As String)

        Me.conectar()
        cmd.CommandText = "delete from " & nombreTabla
        cmd.CommandText &= " where " & campo & " = " & valor & " AND " & campo2 & " = " & valor2

        cmd.ExecuteNonQuery()
        conexion.Close()
    End Sub

    Public Sub eliminar(ByVal campo As String, ByVal valor As String)

        Me.conectar()
        cmd.CommandText = "delete from " & nombreTabla
        cmd.CommandText &= " where " & campo & " = " & valor
        Try
            cmd.ExecuteNonQuery()
        Catch
            MessageBox.Show("El objeto no puede ser eliminado debido a que esta siendo usado por otros elementos del sistema", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
        End Try
        conexion.Close()
        
    End Sub

    Public Sub insertar(ByVal pkAutonumerica As Boolean, ByVal valores As String)
        Dim txt_Insert As String = ""
        txt_Insert = Me.armoInsert(pkAutonumerica)
        txt_Insert += Me.asigno_valores_insert(pkAutonumerica, valores)
        Me.conectar()
        Me.cmd.CommandText = txt_Insert
        cmd.ExecuteNonQuery()
        Me.conexion.Close()
    End Sub



    Private Function armoInsert(ByVal pkAutonumerica As Boolean) As String
        Dim contador As Integer
        Dim tabla As Data.DataTable

        If pkAutonumerica = True Then
            contador = 1
        Else
            contador = 0
        End If

        Dim txt As String = ""
        tabla = Me.leoEstructura()
        txt = "insert into " & Me.nombreTabla & " ("
        While tabla.Columns.Count() > contador
            txt += tabla.Columns(contador).Caption
            contador += 1
            If contador < tabla.Columns.Count() Then
                txt += ", "
            End If
        End While
        txt += ") values ("
        Return txt
    End Function

    Private Function leoEstructura() As Data.DataTable
        Dim comando As String
        Dim tabla As New DataTable
        comando = "select top 1 * from " & Me.nombreTabla
        tabla = Me.consulta(comando)
        Return tabla
    End Function

    Public Sub consultaSinReturn(ByVal comando As String)
        Dim tabla As New Data.DataTable
        Me.conectar()
        Me.cmd.CommandText = comando
        tabla.Load(Me.cmd.ExecuteReader)
        Me.conexion.Close()
    End Sub

    Public Function consulta(ByVal comando As String) As Data.DataTable
        Dim tabla As New Data.DataTable
        Me.conectar()
        Me.cmd.CommandText = comando
        tabla.Load(Me.cmd.ExecuteReader())
        Me.conexion.Close()
        Return tabla
    End Function


    Private Function asigno_valores_insert(ByVal pkAutonumerica As Boolean, ByVal txt_insertar As String) As String
        Dim objeto_tabla As Data.DataTable 'tabla que contiene la estructura en la base 
        Dim contador As Integer 'contador de bucle()
        Dim campo As Integer = 0 'ubicación del(campo)
        Dim coma As Integer = 0 'ubicacion de la(",")
        Dim igual As Integer = 0 'ubicación del("=")
        Dim txt_origen As String = "" 'texto origen 
        Dim nombre_campo As String = "" 'campo a buscar()
        Dim valor As String = "" 'valor del campo()
        Dim txt As String = "" 'texto que se va formando para el insert 
        Dim tipo_dato As String = ""

        If pkAutonumerica = True Then
            contador = 1
        Else
            contador = 0
        End If

        objeto_tabla = Me.leoEstructura()
        txt_origen = txt_insertar.ToUpper.Trim()
        While objeto_tabla.Columns.Count() > contador
            nombre_campo = objeto_tabla.Columns(contador).Caption.ToUpper.Trim()
            tipo_dato = objeto_tabla.Columns(contador).DataType.ToString()
            campo = txt_origen.IndexOf(nombre_campo)
            If campo <> -1 Then
                igual = txt_origen.IndexOf("=", campo)
                coma = txt_origen.IndexOf(",", campo)
            Else
                igual = -1
                coma = -1
            End If
            If campo <> -1 Then
                igual = txt_origen.IndexOf("=", campo)
                coma = txt_origen.IndexOf(",", campo)
                If coma <> -1 Then
                    valor = Me.acomodo_al_tipo_dato(txt_origen.Substring(igual + 1, coma - igual - 1), tipo_dato)
                Else
                    valor = Me.acomodo_al_tipo_dato(txt_origen.Substring(igual + 1), tipo_dato)
                End If
            Else
                valor = "null"
            End If
            txt += valor
            contador += 1
            If contador < objeto_tabla.Columns.Count() Then
                txt += ", "
            End If
        End While
        txt += ")"
        Return txt
    End Function

    Private Function acomodo_al_tipo_dato(ByVal texto As String, ByVal tipo_dato As String) As String
        Select Case tipo_dato
            Case "System.String"
                Return "'" & texto & "'"
            Case "System.Int16"
                Return texto
            Case "System.Int32"
                Return texto
            Case "System.Int64"
                Return texto
            Case "System.Byte"
                Return texto
            Case "System.Double"
                Return texto
            Case "System.Decimal"
                Return texto
            Case "System.DateTime"
                Return "'" & texto & "'"
            Case "System.DateTimeKind"
                Return "'" & texto & "'"
            Case Else
                Return texto
        End Select
    End Function

    Public Sub Modificar(ByVal pkAutonumerica As Boolean, ByVal valores As String, ByVal restriccion As String)
        Dim _update As String = "UPDATE " + Me.nombreTabla + " SET "
        Dim _set As String
        Dim _restriccion As String
        _set = Me.ArmaSetUpdate(pkAutonumerica, valores)
        _restriccion = ArmarRestriccionUpdate(pkAutonumerica, restriccion)
        If _restriccion = "error" Then
            Exit Sub
        End If
        _update &= _set + _restriccion
        Me.conectar()
        Me.cmd.CommandText = _update
        cmd.ExecuteNonQuery()
        Me.conexion.Close()
    End Sub

    Private Function ArmarRestriccionUpdate(ByVal pkAutonumerica As Boolean, ByVal restriccion As String) As String
        Dim objeto_tabla As Data.DataTable 'tabla que contiene la estructura en la base 
        Dim contador As Integer = 0 'contador de bucle()
        Dim c As Integer 'para recorrer las columas buscando una 
        Dim coma As Integer = 0 'ubicacion de la(",")
        Dim igual As Integer = 0 'ubicación del("=")
        Dim txt_origen As String = "" 'texto origen 
        Dim nombre_campo As String = "" 'campo a buscar()
        Dim valor As String = "" 'valor del campo Dim txt As String = "" 'texto que se va formando para el insert 
        Dim txt As String = "" 'texto que se va formando para el insert
        Dim tipo_dato As String = ""
        Dim estado As Boolean = True
        Dim encontro As Boolean = False
        Dim primera_vez As Boolean = True
        objeto_tabla = Me.leoEstructura()
        txt_origen = restriccion.ToUpper.Trim()

        If pkAutonumerica = True Then
            contador = 1
        Else
            contador = 0
        End If

        While estado
            igual = txt_origen.IndexOf("=")
            coma = IIf(txt_origen.IndexOf(",") = -1, txt_origen.Length, txt_origen.IndexOf(","))
            If txt_origen.IndexOf(",") = -1 Then
                estado = False
            End If
            nombre_campo = txt_origen.Substring(0, igual).Trim()
            valor = txt_origen.Substring(igual + 1, coma - igual - 1).Trim()
            tipo_dato = objeto_tabla.Columns(contador).DataType.ToString()
            For c = 0 To objeto_tabla.Columns.Count() - 1
                If nombre_campo.Trim() = objeto_tabla.Columns(c).Caption.ToUpper.Trim() Then
                    tipo_dato = objeto_tabla.Columns(c).DataType.ToString()
                    encontro = True
                End If
            Next
            If encontro Then
                If primera_vez Then
                    txt &= "WHERE " + nombre_campo + " = " + acomodo_al_tipo_dato(valor, tipo_dato) + Chr(13)
                    primera_vez = False
                Else
                    txt &= " AND " + nombre_campo + " = " + acomodo_al_tipo_dato(valor, tipo_dato) + Chr(13)
                End If
            Else
                MsgBox("no existe el nombre de columna " + nombre_campo, MsgBoxStyle.Critical, "Importante")
                txt = "error"
                Exit While
            End If
            If estado = True Then
                txt_origen = txt_origen.Substring(coma + 1)
            End If
        End While
        Return txt
    End Function

    Private Function ArmaSetUpdate(ByVal pkAutonumerica As Boolean, ByVal valores As String) As String
        Dim objeto_tabla As Data.DataTable 'tabla que contiene la estructura en la base 
        Dim contador As Integer 'contador de bucle()
        Dim campo As Integer = 0 'ubicación del(campo)
        Dim coma As Integer = 0 'ubicacion de la(",")
        Dim igual As Integer = 0 'ubicación del("=")
        Dim txt_origen As String = "" 'texto origen 
        Dim nombre_campo As String = "" 'campo a buscar()
        Dim valor As String = "" 'valor del campo()
        Dim txt As String = "" 'texto que se va formando para el insert 
        Dim tipo_dato As String = ""

        If pkAutonumerica = True Then
            contador = 1
        Else
            contador = 0
        End If

        objeto_tabla = Me.leoEstructura()
        txt_origen = valores.ToUpper.Trim()
        While objeto_tabla.Columns.Count() > contador
            nombre_campo = objeto_tabla.Columns(contador).Caption.ToUpper.Trim()
            tipo_dato = objeto_tabla.Columns(contador).DataType.ToString()
            campo = txt_origen.IndexOf(nombre_campo)
            If campo <> -1 Then
                igual = txt_origen.IndexOf("=", campo)
                coma = txt_origen.IndexOf(",", campo)
            Else
                igual = -1
                coma = -1
            End If
            If campo <> -1 Then
                igual = txt_origen.IndexOf("=", campo)
                coma = txt_origen.IndexOf(",", campo)
                If coma <> -1 Then
                    valor = objeto_tabla.Columns(contador).Caption & "="
                    valor += Me.acomodo_al_tipo_dato(txt_origen.Substring(igual + 1, coma - igual - 1), tipo_dato)
                Else
                    valor = objeto_tabla.Columns(contador).Caption & "="
                    valor += Me.acomodo_al_tipo_dato(txt_origen.Substring(igual + 1), tipo_dato)
                End If
            Else
                valor = "null"
            End If
            txt += valor
            contador += 1
            If contador < objeto_tabla.Columns.Count() Then
                txt += ", "
            End If
        End While
        txt += " "
        Return txt

    End Function

    Public Function obtenerProducto(ByVal id As String) As Data.DataTable
        Dim tabla As New Data.DataTable
        Me.conectar()
        cmd.CommandText = "select * from producto where idProducto = " & id
        tabla.Load(cmd.ExecuteReader())
        conexion.Close()
        Return tabla
    End Function

    Public Function obtenerProveedor(ByVal campo As String, ByVal valor As String) As Data.DataTable
        Dim tabla As New Data.DataTable
        Me.conectar()
        cmd.CommandText = "select * from Proveedor p where p." & campo & " LIKE UPPER('%" & valor & "%')"
        tabla.Load(cmd.ExecuteReader())
        conexion.Close()

        Return tabla
    End Function

    Public Function obtenerUsuario(ByVal id As String) As Data.DataTable
        Dim tabla As New Data.DataTable
        Me.conectar()
        cmd.CommandText = "SELECT Usuario.idUsuario, Usuario.IdEmpleado, Usuario.pass, Usuario.nombre FROM Usuario where idUsuario = " & id
        tabla.Load(cmd.ExecuteReader())
        conexion.Close()
        Return tabla
    End Function

    Public Sub insertarDetalleVenta(ByVal idVenta As String, ByVal idProducto As String, ByVal talle As String, ByVal cantidad As String)
        Me.conectar()
        cmd.CommandText = "INSERT INTO DetalleVenta VALUES(" & idVenta & ", " & idProducto & ", " & talle & ", " & cantidad & ", NULL)"
        cmd.ExecuteReader()
        conexion.Close()
    End Sub

    Public Sub insetarPagos(ByVal idVenta As String, ByVal idTipoPago As String, ByVal idTarjeta As String, ByVal nroAuto As String, ByVal Monto As String)
        Me.conectar()


        If idTarjeta Is Nothing Then
            cmd.CommandText = "INSERT INTO Pagos VALUES( " & idVenta & ",  " & idTipoPago & ", NULL, NULL, " & Monto & ")"
            cmd.ExecuteReader()
            conexion.Close()
        Else
            cmd.CommandText = "INSERT INTO Pagos VALUES( " & idVenta & ",  " & idTipoPago & ", " & idTarjeta & ", " & nroAuto & " , " & Monto & ")"
            cmd.ExecuteReader()
            conexion.Close()
        End If


    End Sub

    Public Sub actualizarStockVenta(ByVal idProducto As String, ByVal idTalle As String, ByVal cantComprada As String)
        Me.conectar()
        cmd.CommandText = "UPDATE Talle_Producto SET cantidad = cantidad - " & cantComprada & " WHERE idProducto = " & idProducto & " and idTalle = " & idTalle

        cmd.ExecuteReader()
        conexion.Close()

    End Sub
    Public Sub actualizarStockCompra(ByVal idProducto As String, ByVal idTalle As String, ByVal cantComprada As Integer)

        talleExiste(idProducto, idTalle)
        Me.conectar()
        Dim cantidad As Integer
        cmd.CommandText = "Select SUM(cantidad) FROM Talle_Producto where idProducto =" & idProducto & " and idTalle = " & idTalle
        Dim tabla As New DataTable
        tabla.Load(cmd.ExecuteReader())

        cantidad = CInt(tabla.Rows(0)(0))
        cantidad = cantidad + cantComprada

        cmd.CommandText = "UPDATE Talle_Producto SET cantidad = " & cantidad & "WHERE idProducto = " & idProducto & " and idTalle = " & idTalle

        cmd.ExecuteReader()
        conexion.Close()
        sumarStockTotal(idProducto)
        conexion.Close()

    End Sub
    Private Sub talleExiste(ByVal idProducto As String, ByVal idTalle As String)
        Me.conectar()

        Dim tabla As New DataTable
        cmd.CommandText = "Select COUNT(*) FROM Talle_Producto where idProducto =" & idProducto & " and idTalle = " & idTalle
        tabla.Load(cmd.ExecuteReader())
        If tabla.Rows(0)(0) = 0 Then
            cmd.CommandText = "Insert into Talle_Producto Values(" & idProducto & ", " & idTalle & ", 0)"
            cmd.ExecuteReader()
        End If

        conexion.Close()
    End Sub

    Private Sub sumarStockTotal(ByVal idProducto)
        Dim cantidad As Integer
        Me.conectar()
        Dim tabla As New DataTable
        cmd.CommandText = "Select SUM(cantidad) FROM Talle_Producto where idProducto =" & idProducto
        tabla.Load(cmd.ExecuteReader())
        cantidad = CInt(tabla.Rows(0)(0))
        cmd.CommandText = "UPDATE Producto SET cantidad= " & cantidad & " where idProducto=" & idProducto
        cmd.ExecuteReader()
        conexion.Close()

    End Sub



End Class
